package cn.topevery.garden.dal.dao.event;

import cn.hutool.core.collection.CollectionUtil;
import cn.topevery.garden.entity.dto.event.SerialNoDTO;
import cn.topevery.garden.entity.po.event.EventInfo;
import cn.topevery.garden.entity.query.EvaluationQuery;
import cn.topevery.garden.entity.query.EventInfoQuery;
import cn.topevery.garden.entity.vo.event.EventInfoVO;
import cn.topevery.garden.framework.base.BaseDaoImpl;
import hw.topevery.basis.entity.po.SysUser;
import hw.topevery.framework.db.CommandType;
import hw.topevery.framework.db.DbCommand;
import hw.topevery.framework.db.DbScriptHelper;
import hw.topevery.framework.db.entity.DataPager;
import hw.topevery.framework.db.entity.SqlQueryMate;
import hw.topevery.framework.db.enums.ScriptConditionEnum;
import hw.topevery.framework.entity.DbCsp;
import hw.topevery.framework.entity.KeyValue;
import hw.topevery.framework.entity.Value;
import hw.topevery.framework.enums.DbTypeEnum;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicBoolean;
import java.util.stream.Collectors;

/**
 * 案件表
 *
 * @author code-generate
 * @date 2022-08-23
 */
@Repository
public class EventInfoDaoImpl extends BaseDaoImpl<EventInfo> implements EventInfoDao {
    @Override
    public <P extends DbCsp> List<EventInfo> getSearch(P para) {
        return null;
    }


    @Override
    public DataPager<EventInfoVO> listPage(EventInfoQuery query, SysUser user, List<String> deptIds) {
        String table = "t_event_info tei \n" +
                "join v_wf_process_info vwpi on vwpi.c_workflow_instance_id=tei.c_wf_inst_id\n" +
                "left join t_section ts on tei.c_section_id=ts.c_id\n" +
                "left join t_company tc on tei.c_company_id=tc.c_id\n" +
                "left join t_sys_class tsc on tei.c_type_id = tsc.c_class_id\n";

        String columns = "tei.*,\n" +
                "vwpi.c_activity_instance_id actInstId,\n" +
                "vwpi.c_signed_user_id signedUserId,\n" +
                "vwpi.c_signed_date signedDate,\n" +
                "vwpi.c_should_finished_date actShouldFinishDate,\n" +
                "vwpi.c_prev_activity_instance_id prevActInstId,\n" +
                "vwpi.c_activity_identifier activityIdentifier,\n" +
                "vwpi.c_in_date inDate,\n" +
                "ts.c_name sectionName,\n" +
                "tc.c_name companyName,\n" +
                "tsc.c_name typeName\n";

        int pageIndex = query.pageIndex - 1;
        int pageSize = query.pageSize;

        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns).setOrderBy("tei.c_id desc");
        sqlQueryMate
                .where(true, "tei.c_db_status", ScriptConditionEnum.Eq, 0)
                .where(true, "tei.c_status", ScriptConditionEnum.NotEq, 2)
                .where(StringUtils.isNotBlank(query.getCode()), "tei.c_code", ScriptConditionEnum.Like, query.getCode())
                .where(StringUtils.isNotBlank(query.getSectionId()), "tei.c_section_id", ScriptConditionEnum.Eq, query.getSectionId())
                .where(user != null && user.userIsAdmin != 1 && CollectionUtils.isNotEmpty(deptIds), "vwpi.c_receive_object_id", ScriptConditionEnum.In, deptIds)
                .where(StringUtils.isNotBlank(query.getReportId()), "tei.c_report_id", ScriptConditionEnum.Eq, query.getReportId());
        return getDataPager(sqlQueryMate, pageSize, pageIndex, EventInfoVO.class);
    }

    @Override
    public EventInfo getEntityByColumn(String key, String value) {
        KeyValue<String> map = new KeyValue<>();
        map.key = key;
        map.value = value;
        return getEntityByColumnEqValue(map);
    }

    @Override
    public SerialNoDTO getEvtCode(String year, String month, String table) {
        /*Value<SerialNoDTO> res = new Value<>();
        run("p_sys_get_serial_no", CommandType.StoredProcedure, dbCommand -> {
            dbCommand.addInParameter("pi_year", year);
            dbCommand.addInParameter("pi_month", month);
            dbCommand.addInParameter("pi_table_name", table);
            res.data = dbCommand.executeToEntity(SerialNoDTO.class);
        });
        return res.data;*/
        Value<SerialNoDTO> res = new Value<>();
        String sql = "SELECT REPLACE(REPLACE(c_mode,'@year',:year),'@serial',RIGHT(CONCAT('00000',c_cur_serial_no ),6)) title FROM t_sys_serial a  WHERE a.c_table_name = :table AND a.c_year = :year;";
        run(sql, CommandType.Text, dbCmd -> {
            dbCmd.addInParameter("year", DbTypeEnum.VARCHAR, year);
            dbCmd.addInParameter("table", DbTypeEnum.VARCHAR, table);
            res.data = dbCmd.executeToEntity(SerialNoDTO.class);
        });
        AtomicBoolean flag = new AtomicBoolean(false);
        if (res.data != null) {
            String updateSql = " update t_sys_serial a set  c_cur_serial_no =c_cur_serial_no+c_step where a.c_table_name = :table AND a.c_year = :year;";
            run(updateSql, CommandType.Text, dbCmd -> {
                dbCmd.addInParameter("year", DbTypeEnum.VARCHAR, year);
                dbCmd.addInParameter("table", DbTypeEnum.VARCHAR, table);
                dbCmd.executeNonQuery();
            });
        } else {
            String insertSql = " insert into T_SYS_SERIAL\n" +
                    "(c_year, c_month, c_table_name, c_cur_serial_no, c_step, c_mode, c_desc, c_db_last_update_id, c_db_last_update_date)\n" +
                    " VALUES (:year, NULL,:table, 1, 1, '九龙坡@year字第@serial号', '九龙坡@year字第@serial号', NULL, NULL);";
            run(insertSql, CommandType.Text, dbCmd -> {
                dbCmd.addInParameter("year", DbTypeEnum.VARCHAR, year);
                dbCmd.addInParameter("table", DbTypeEnum.VARCHAR, table);
                flag.set(dbCmd.executeNonQuery());
            });
            if (flag.get()) {
                run(sql, CommandType.Text, dbCmd -> {
                    dbCmd.addInParameter("year", DbTypeEnum.VARCHAR, year);
                    dbCmd.addInParameter("table", DbTypeEnum.VARCHAR, table);
                    res.data = dbCmd.executeToEntity(SerialNoDTO.class);
                });
                String updateSql = " update t_sys_serial a set  c_cur_serial_no =c_cur_serial_no+c_step where a.c_table_name = :table AND a.c_year = :year;";
                run(updateSql, CommandType.Text, dbCmd -> {
                    dbCmd.addInParameter("year", DbTypeEnum.VARCHAR, year);
                    dbCmd.addInParameter("table", DbTypeEnum.VARCHAR, table);
                    flag.set(dbCmd.executeNonQuery());
                });
            }
        }
        return res.data;
    }

    @Override
    public EventInfoVO getEventDetail(EventInfoQuery query) {
        String table = "t_event_info tei \n" +
                "join v_wf_process_info vwpi on vwpi.c_workflow_instance_id=tei.c_wf_inst_id\n" +
                "left join t_section ts on tei.c_section_id=ts.c_id\n" +
                "left join t_company tc on tei.c_company_id=tc.c_id\n" +
                "left join t_sys_class tsc on tei.c_type_id = tsc.c_class_id\n";
        String columns = "tei.*,\n" +
                "vwpi.c_activity_instance_id actInstId,\n" +
                "vwpi.c_signed_user_id signedUserId,\n" +
                "vwpi.c_in_date inDate,\n" +
                "vwpi.c_signed_date signedDate,\n" +
                "vwpi.c_should_finished_date actShouldFinishDate,\n" +
                "vwpi.c_prev_activity_instance_id prevActInstId,\n" +
                "vwpi.c_activity_identifier activityIdentifier,\n" +
                "vwpi.c_in_date inDate,\n" +
                "ts.c_name sectionName,\n" +
                "tc.c_name companyName,\n" +
                "tsc.c_name typeName\n";
        SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        sqlQueryMate.where(true, "tei.c_db_status", ScriptConditionEnum.Eq, 0)
                .where(query.getStatus() != null, "tei.c_status", ScriptConditionEnum.Eq, query.getStatus())
                .where(StringUtils.isNotBlank(query.getEvtId()), "tei.c_evt_id", ScriptConditionEnum.Eq, query.getEvtId());
        return selectOne(sqlQueryMate, EventInfoVO.class);
    }

    @Override
    public DataPager<EventInfoVO> getEvtSearchPage(EventInfoQuery query, SysUser user, List<String> deptIds) {
        String table = "t_event_info tei \n" +
                "left join v_wf_process_info vwpi on vwpi.c_workflow_instance_id=tei.c_wf_inst_id\n" +
                "left join t_wf_activity_instance_bak twaib on twaib.c_workflow_instance_id=tei.c_wf_inst_id and twaib.c_activity_identifier ='F883FB9B-3B22-400B-96AF-AB0723BBD92A'\n" +
                "left join t_section ts on tei.c_section_id=ts.c_id\n" +
                "left join t_company tc on tei.c_company_id=tc.c_id\n" +
                "left join t_sys_class tsc on tei.c_type_id = tsc.c_class_id\n";
        String columns = "tei.*,\n" +
                "IFNULL(vwpi.c_activity_instance_id, twaib.c_activity_identifier) actInstId,\n" +
                "IFNULL(vwpi.c_signed_user_id, twaib.c_signed_user_id) signedUserId,\n" +
                "IFNULL(vwpi.c_signed_date, twaib.c_signed_date) signedDate,\n" +
                "IFNULL(vwpi.c_should_finished_date, twaib.c_should_finished_date) actShouldFinishDate,\n" +
                "IFNULL(vwpi.c_prev_activity_instance_id, twaib.c_prev_activity_instance_id) prevActInstId,\n" +
                "IFNULL(vwpi.c_activity_identifier, twaib.c_activity_identifier) activityIdentifier,\n" +
                "IFNULL(vwpi.c_in_date, twaib.c_in_date) inDate,\n" +
                "ts.c_name sectionName,\n" +
                "tc.c_name companyName,\n" +
                "tsc.c_name typeName\n";
        int pageIndex = query.pageIndex - 1;
        int pageSize = query.pageSize;
        /*SqlQueryMate sqlQueryMate = new SqlQueryMate(table).setColumns(columns);
        sqlQueryMate.where(true, "tei.c_db_status", ScriptConditionEnum.Eq, 0)
                .where(query.getStatus() != null, "tei.c_status", ScriptConditionEnum.Eq, query.getStatus())
                .where(StringUtils.isNotBlank(query.getCode()), "tei.c_code", ScriptConditionEnum.Like, query.getCode())
                .where(StringUtils.isNotBlank(query.getReportId()), "tei.c_report_id", ScriptConditionEnum.Eq, query.getReportId())
                .where(StringUtils.isNotBlank(query.getSectionId()), "tei.c_section_id", ScriptConditionEnum.Eq, query.getSectionId())
                .where(query.getCompanyId() != null, "tei.c_company_id", ScriptConditionEnum.Eq, query.getCompanyId())
                .where(StringUtils.isNotBlank(query.beginDate), "tei.c_report_date", ScriptConditionEnum.GreaterThanEq, query.beginDate)
                .where(StringUtils.isNotBlank(query.endDate), "tei.c_report_date", ScriptConditionEnum.LessThanEq, query.endDate);*/
        String orderBy = " tei.c_db_create_date desc ";
        List<KeyValue<Object>> parameters = new ArrayList<>();
        StringBuilder where = new StringBuilder();
        where.append(" tei.c_db_status = 0\n");
        if (query.getStatus() != null) {
//            where.append(" and tei.c_status = :p_status ");
//            parameters.add(new KeyValue<>("p_status", query.getStatus()));
            if (query.getStatus().equals(1)) {
                where.append(" and tei.c_cur_act_name='完结' ");
            }
            if (query.getStatus().equals(0)) {
                where.append(" and tei.c_cur_act_name='处置' ");
            }
        }
        if (StringUtils.isNotBlank(query.getCode())) {
            where.append(" and tei.c_code like :p_code ");
            parameters.add(new KeyValue<>("p_code", "%" + query.getCode() + "%"));
        }
        if (StringUtils.isNotBlank(query.getReportId())) {
            where.append(" and tei.c_report_id = :p_report_id ");
            parameters.add(new KeyValue<>("p_report_id", query.getReportId()));
        }
        if (StringUtils.isNotBlank(query.getSectionId())) {
            where.append(" and tei.c_section_id = :p_section_id ");
            parameters.add(new KeyValue<>("p_section_id", query.getSectionId()));
        }
        if (query.getCompanyId() != null) {
            where.append(" and tei.c_company_id = :p_company_id ");
            parameters.add(new KeyValue<>("p_company_id", query.getCompanyId()));
        }
        if (StringUtils.isNotBlank(query.beginDate)) {
            where.append(" and tei.c_report_date >= :p_begin_date ");
            parameters.add(new KeyValue<>("p_begin_date", query.beginDate));
        }
        if (StringUtils.isNotBlank(query.endDate)) {
            where.append(" and tei.c_report_date <= :p_end_date ");
            parameters.add(new KeyValue<>("p_end_date", query.endDate));
        }
        if (CollectionUtils.isNotEmpty(deptIds) && user != null && user.userIsAdmin != 1) {
            String deptIdStr = StringUtils.join(deptIds.stream().map(e -> "'" + e + "'").collect(Collectors.toList()), ",");
            where.append(" and EXISTS\n" +
                    "(SELECT twpi.c_receive_object_id FROM t_wf_activity_instance twpi WHERE twpi.c_workflow_instance_id=tei.c_wf_inst_id \n" +
                    "and twpi.c_receive_object_id in (" + deptIdStr + "))\n" +
                    "OR EXISTS \n" +
                    "(SELECT twaib.c_receive_object_id FROM t_wf_activity_instance_bak twaib WHERE twaib.c_workflow_instance_id=tei.c_wf_inst_id \n" +
                    "and twaib.c_receive_object_id in (" + deptIdStr + ")) ");
        }
        String sql = DbScriptHelper.getSelectSqlPage(table, columns, where.toString(), orderBy, pageSize, pageIndex);
        return getDataPagerByCustomize(sql, CommandType.Text, parameters, EventInfoVO.class);
    }

    @Override
    public boolean updateByColumns(String userId, List<KeyValue> setList, List<KeyValue> whereList) {
        return super.updateByTable(userId, setList, whereList);
    }

    @Override
    public EventInfoVO getInfoById(Integer id) {
        SqlQueryMate sqlQueryMate = new SqlQueryMate("t_event_info tei \n" +
                "left join v_wf_process_info vwpi on vwpi.c_workflow_instance_id=tei.c_wf_inst_id\n" +
                "left join t_wf_activity_instance_bak twaib on twaib.c_workflow_instance_id=tei.c_wf_inst_id and twaib.c_activity_identifier ='F883FB9B-3B22-400B-96AF-AB0723BBD92A'\n" +
                "left join t_section ts on tei.c_section_id=ts.c_id\n" +
                "left join t_company tc on tei.c_company_id=tc.c_id\n" +
                "left join t_sys_class tsc on tei.c_type_id = tsc.c_class_id\n")
                .setColumns("tei.*,\n" +
                        "IFNULL(vwpi.c_activity_instance_id, twaib.c_activity_identifier) actInstId,\n" +
                        "IFNULL(vwpi.c_signed_user_id, twaib.c_signed_user_id) signedUserId,\n" +
                        "IFNULL(vwpi.c_signed_date, twaib.c_signed_date) signedDate,\n" +
                        "IFNULL(vwpi.c_should_finished_date, twaib.c_should_finished_date) actShouldFinishDate,\n" +
                        "IFNULL(vwpi.c_prev_activity_instance_id, twaib.c_prev_activity_instance_id) prevActInstId,\n" +
                        "IFNULL(vwpi.c_activity_identifier, twaib.c_activity_identifier) activityIdentifier,\n" +
                        "IFNULL(vwpi.c_in_date, twaib.c_in_date) inDate,\n" +
                        "ts.c_name sectionName,\n" +
                        "tc.c_name companyName,\n" +
                        "tsc.c_name typeName\n");
        sqlQueryMate.where(true, "tei.c_db_status", ScriptConditionEnum.Eq, 0)
                .where(id != null, "tei.c_id", ScriptConditionEnum.Eq, id);
        return selectOne(sqlQueryMate, EventInfoVO.class);
    }

    @Override
    public List<Map<String, Object>> getSysClassList(List<String> typeIdList) {
        String param = StringUtils.join(typeIdList.stream().map(e -> "'" + e + "'").collect(Collectors.toList()), ",");
        String sql = "SELECT c_name typeName FROM t_sys_class WHERE c_class_id in (" + param + ") ORDER BY FIELD(c_class_id," + param + ")";
        return getMap(sql);
    }

    @Override
    public void addShouldFinishTime(Integer selfDefLimit) {
        String sql = "update t_event_info set c_should_finished_time=date_add(c_should_finished_time, interval " + selfDefLimit + " second);";
        run(sql, CommandType.Text, DbCommand::executeNonQuery);
    }

    @Override
    public List<Map<String, Object>> eventCount(String dateFormat, String timeSlot) {
        String sql = "SELECT\n" +
                "  t.date,\n" +
                "  sum( CASE WHEN t.`status` = 0 THEN t.count ELSE 0 END ) AS registerNum,\n" +
                "  sum( CASE WHEN t.`status` = 1 THEN t.count ELSE 0 END ) AS disposeNum,\n" +
                "  sum( CASE WHEN t.`status` = 2 THEN t.count ELSE 0 END ) AS completeNum \n" +
                "FROM\n" +
                "  (\n" +
                "  SELECT\n" +
                "    count(*) count,\n" +
                "    c_status `status`,\n" +
                "    date_format( c_cur_act_time, " + dateFormat + " ) date \n" +
                "  FROM\n" +
                "    t_event_info \n" +
                "  WHERE\n" +
                "    c_db_status = 0 \n" +
                "    AND DATE_SUB( CURDATE(), INTERVAL " + timeSlot + " ) <= date( c_cur_act_time ) \n" +
                "  GROUP BY\n" +
                "    c_status,\n" +
                "    date_format( c_cur_act_time, " + dateFormat + " ) \n" +
                "  ) t \n" +
                "GROUP BY\n" +
                "  t.date";
        return getMap(sql);
    }

    @Override
    public List<Map<String, Object>> getEventTaskCount() {
        return getMap("SELECT t.*,\n" +
                " ROUND((1 - t.pendingCount / t.total) * 100, 2) completionRate\n" +
                "FROM (\n" +
                "SELECT\n" +
                " SUBSTRING(c_type_id, 38, 36) typeId,\n" +
                " CASE SUBSTRING(c_type_id, 38, 36) WHEN '1B3E5DD2-7DFE-4C0B-A546-C6076CCDF6BB' THEN '其他任务'\n" +
                " ELSE '巡查任务' END AS typeName,\n" +
                " IFNULL( COUNT(*), 0 ) total,\n" +
                " COUNT( CASE c_status WHEN 2 THEN NULL ELSE 1 END ) pendingCount \n" +
                "FROM\n" +
                " `t_event_info` \n" +
                "WHERE\n" +
                " c_db_status = 0 \n" +
                " AND SUBSTRING( c_type_id, 38, 36 ) \n" +
                " IN ( '1B3E5DD2-7DFE-4C0B-A546-C6076CCDF6BB', '314E2389-A440-4F33-A338-8C6C7484BC10' ) \n" +
                "GROUP BY\n" +
                " c_type_id ) t");
    }

    @Override
    public DataPager<Map<String, Object>> getStatisticList(EvaluationQuery query) {
        String columns = "t.*, (t.dailyValue + t.liveValue + t.internalValue + t.specialValue) totalScore\n";
        StringBuffer tableBuffer = new StringBuffer(
                "(SELECT \n" +
                        "tc.c_section_id sectionId,\n" +
                        "tc.c_id companyId,\n" +
                        "ts.c_name sectionName,\n" +
                        "tc.c_name companyName,\n" +
                        "ROUND((100 - IFNULL(SUM(tei.c_actual_kf_value),0))*0.4, 1) dailyValue,\n" +
                        "ROUND((100 - IFNULL(SUM(ttos.c_score),0))*0.3, 1) liveValue,\n" +
                        "ROUND((100 - IFNULL(SUM(ttd.c_score),0))*0.3, 1) internalValue,\n" +
                        "IFNULL(SUM(tss.c_score),0) specialValue\n" +
                        "FROM t_company tc\n" +
                        "JOIN t_section ts on ts.c_id=tc.c_section_id\n" +
                        "LEFT JOIN t_event_info tei ON tei.c_db_status=0 AND tei.c_section_id=tc.c_section_id AND tei.c_company_id=tc.c_id\n"
        );
        if (StringUtils.isNotBlank(query.beginDate) && StringUtils.isNotBlank(query.endDate)) {
            tableBuffer.append("AND tei.c_db_create_date BETWEEN '" + query.beginDate + "' AND '" + query.endDate + "'\n");
        }
        tableBuffer.append("LEFT JOIN t_task_on_site ttos ON ttos.c_db_status=0 AND ttos.c_section_id=tc.c_section_id AND ttos.c_company_id=tc.c_id\n");
        if (StringUtils.isNotBlank(query.beginDate) && StringUtils.isNotBlank(query.endDate)) {
            tableBuffer.append("AND ttos.c_evaluation_time BETWEEN '" + query.beginDate + "' AND '" + query.endDate + "'\n");
        }
        tableBuffer.append("LEFT JOIN t_task_document ttd ON ttd.c_db_status=0 AND ttd.c_section_id=tc.c_section_id AND ttd.c_company_id=tc.c_id\n");
        if (StringUtils.isNotBlank(query.beginDate) && StringUtils.isNotBlank(query.endDate)) {
            tableBuffer.append("AND ttd.c_evaluation_time BETWEEN '" + query.beginDate + "' AND '" + query.endDate + "'\n");
        }
        tableBuffer.append("LEFT JOIN t_special_score tss ON tss.c_db_status=0 AND tss.c_section_id=tc.c_section_id AND tss.c_company_id=tc.c_id\n");
        if (StringUtils.isNotBlank(query.beginDate) && StringUtils.isNotBlank(query.endDate)) {
            tableBuffer.append("AND tss.c_evaluation_time BETWEEN '" + query.beginDate + "' AND '" + query.endDate + "'\n");
        }
        tableBuffer.append("WHERE tc.c_db_status=0\n");
        if (StringUtils.isNotBlank(query.getSectionId())) {
            tableBuffer.append("AND tc.c_section_id = '" + query.getSectionId() + "'\n");
        }
        if (query.getCompanyId() != null) {
            tableBuffer.append("AND tc.c_id = '" + query.getCompanyId() + "'\n");
        }
        tableBuffer.append("GROUP BY tc.c_section_id,tc.c_id ORDER BY ts.c_id) t limit " + ((query.pageIndex - 1) * query.pageSize) + "," + query.pageSize);
        String querySql = "SELECT " + columns + " FROM " + tableBuffer.toString();
        DataPager<Map<String, Object>> dataPager = new DataPager<>();
        dataPager.records = getMap(querySql);
        String countSql = "SELECT count(*) total FROM " + tableBuffer.toString();
        Map<String, Object> countMap = getOneMap(countSql);
        if (CollectionUtil.isNotEmpty(countMap)) {
            dataPager.total = ((long) countMap.getOrDefault("total", 0));
        }
        return dataPager;
    }

    @Override
    public List<Map<String, Object>> getEvtStat(EventInfoQuery query) {
        String param = "";
        param += " and b.c_in_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) ";
        String sql = "select aa.day,bb.c_activity_name,ifnull(cc.c_cnt,0) c_cnt\n" +
                "from ( \n" +
                "select  \n" +
                "DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL a.help_topic_id day),'%m-%d') day \n" +
                "from mysql.help_topic a \n" +
                "where a.help_topic_id<7 \n" +
                ") aa\n" +
                "join (\n" +
                "\tselect '上报' c_activity_name\n" +
                "\tunion select '审核'\n" +
                "\tunion select '完结'\n" +
                ") bb on 1=1\n" +
                "left join (\n" +
                "select DATE_FORMAT(b.c_in_date,'%m-%d') day,b.c_activity_identifier,b.c_activity_name,count(distinct a.c_id) c_cnt \n" +
                " from t_event_info a \n" +
                " join v_wf_activity_instance b on a.c_wf_inst_id = b.c_workflow_instance_id   \n" +
                " where a.c_db_status=0 \n" +
                param + "\n" +
                " and b.c_activity_identifier in ('E495C80C-469B-4A9B-8353-C4A22CDA4BE1','2afa42c3-9145-24cd-0d8f-9e73c6f43369','F883FB9B-3B22-400B-96AF-AB0723BBD92A') \n" +
                " group by DATE_FORMAT(b.c_in_date,'%m-%d'),b.c_activity_identifier ,b.c_activity_name \n" +
                ") cc on aa.day = cc.day and bb.c_activity_name = cc.c_activity_name\n" +
                "order by aa.day";
        if (query.getDateType().equals(1)) {
            param += " and b.c_in_date >= DATE_SUB(CURDATE(), INTERVAL 6 month) ";
            sql = "select aa.day,bb.c_activity_name,ifnull(cc.c_cnt,0) c_cnt\n" +
                    "from ( \n" +
                    "select  \n" +
                    "DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL a.help_topic_id MONTH),'%Y-%m') day \n" +
                    "from mysql.help_topic a \n" +
                    "where a.help_topic_id<6 \n" +
                    ") aa\n" +
                    "join (\n" +
                    "\tselect '上报' c_activity_name\n" +
                    "\tunion select '审核'\n" +
                    "\tunion select '完结'\n" +
                    ") bb on 1=1\n" +
                    "left join (\n" +
                    "select DATE_FORMAT(b.c_in_date,'%Y-%m') day,b.c_activity_identifier,b.c_activity_name,count(distinct a.c_id) c_cnt \n" +
                    " from t_event_info a \n" +
                    " join v_wf_activity_instance b on a.c_wf_inst_id = b.c_workflow_instance_id   \n" +
                    " where a.c_db_status=0 \n" +
                    param + "\n" +
                    " and b.c_activity_identifier in ('E495C80C-469B-4A9B-8353-C4A22CDA4BE1','2afa42c3-9145-24cd-0d8f-9e73c6f43369','F883FB9B-3B22-400B-96AF-AB0723BBD92A') \n" +
                    " group by DATE_FORMAT(b.c_in_date,'%Y-%m'),b.c_activity_identifier ,b.c_activity_name \n" +
                    ") cc on aa.day = cc.day and bb.c_activity_name = cc.c_activity_name\n" +
                    "order by aa.day"
            ;

        }
        return getMap(sql);
    }

    @Override
    public List<Map<String, Object>> getDiseaseStatsByDistrictId(EventInfoQuery query) {
        String sql = "WITH pre AS (\n" +
                "    SELECT c_district_id, c_small_class_id, c_cur_act_id, c_cur_act_name, count(*) AS total\n" +
                "    FROM t_event_info tei\n" +
                "    WHERE c_db_status = 0\n" +
                "      AND c_big_class_id = '5F04A9E9-3C74-44CB-9BA1-58E08984CBCF'\n" +
                "    GROUP BY c_district_id, c_small_class_id, c_cur_act_id, c_status, c_cur_act_name\n" +
                ")\n" +
                "SELECT pre.c_district_id AS districtId, tsm.c_name AS districtName\n" +
                "     , pre.c_small_class_id AS classId, tsc.c_name AS className\n" +
                "     , pre.c_cur_act_id AS curActId, pre.c_cur_act_name AS curActName, pre.total\n" +
                "FROM pre\n" +
                "         LEFT JOIN t_sys_map tsm ON pre.c_district_id = tsm.c_map_id AND tsm.c_db_status = 0\n" +
                "         LEFT JOIN t_sys_class tsc ON pre.c_small_class_id = tsc.c_class_id AND tsc.c_db_status = 0";
        if (StringUtils.isNotBlank(query.getDistrictId())) {
            sql = sql + "WHERE c_district_id ='" + query.getDistrictId() + "'";
        }
        return getMap(sql);
    }

}
